Generating Watermarks in Excel with .NET

TLDR

• Excel does not have a built-in watermark feature; it can be simulated by setting a "full-page transparent background image."
• Watermarks can be set via Background for "Normal" and "Page Layout" views, and via Header for "Page Layout" and "Print" views.
• When generating watermark images, the image size must be adjusted according to the Excel PaperSize; for landscape orientation, width and height must be swapped.
• Since EPPlus 6 removed the System.Drawing.Common dependency, related implementation methods may no longer work.
• NPOI can achieve watermark effects by manipulating the underlying XML structure (VML Drawing) via low-level APIs.

Strategies for Simulating Watermarks

Excel does not have a native watermark feature. In practice, this can be achieved by setting a full-page background image. Depending on the view mode, the configuration methods are as follows:

• Normal and Page Layout views: Set the image in Background.
• Page Layout and Print: Set the image in Header.
• Page Break Preview: Currently, there is no effective method.

Generating Full-Page Watermark Images

Since Excel's page setup (orientation and size) determines the dimensions of the background image, developers must adjust the image size based on the PaperSize.

Obtaining Paper Size

You can obtain the RawKind and corresponding dimensions for various paper types via System.Drawing.Printing.PrinterSettings.

PrinterSettings settings = new PrinterSettings() {
    PrinterName = "Microsoft XPS Document Writer"
};
foreach (System.Drawing.Printing.PaperSize printerPaperSize in settings.PaperSizes) {
    // Get paper name, width, and height
}

Adjusting Image Dimensions

If the image size does not match the page size, scaling or padding is required.

public Image ResizeImageBackgroundToFullPage(Image watermark, int width, int height){
    if (watermark.Width > width || watermark.Height > height) {
        using (Image image = ZoomOutImage(width, height)) {
            return ResizeImageBackgroundToFullPageInternal(width, height, image);
        }
    }
    return ResizeImageBackgroundToFullPageInternal(width, height, watermark);
}

private Image ResizeImageBackgroundToFullPageInternal(int pageWidth, int pageHeight, Image image) {
    Image bitmap = new Bitmap(pageWidth, pageHeight);
    using Graphics graphics = Graphics.FromImage(bitmap);
    graphics.Clear(Color.White);
    graphics.DrawImage(image, (pageWidth - image.Width) / 2, (pageHeight - image.Height) / 2);
    graphics.CompositingQuality = CompositingQuality.HighQuality;
    graphics.SmoothingMode = SmoothingMode.HighQuality;
    graphics.Save();
    return bitmap;
}

Generating Watermarks Using EPPlus

In older versions of EPPlus that support System.Drawing.Common, you can set it as follows:

sheet.HeaderFooter.OddHeader.InsertPicture(watermark, PictureAlignment.Centered);
sheet.BackgroundImage.Image = watermark;

WARNING

This approach may not be applicable to EPPlus 6, as that version has removed the dependency on System.Drawing.Common.

Generating Watermarks Using NPOI (XLSX)

NPOI does not provide a direct API for setting watermarks; you must manipulate the underlying VML (Vector Markup Language) structure to achieve this.

Implementing VML Drawing Objects

Since the default XSSFVMLDrawing is only used for comments, you need to define a custom class to handle watermark images.

private class VmlDrawing : POIXMLDocumentPart {
    public string PictureRelId { get; set; }
    public Image Image { get; set; }

    protected override void Commit() {
        PackagePart part = GetPackagePart();
        Stream @out = part.GetOutputStream();
        Write(@out);
        @out.Close();
    }

    private void Write(Stream stream) {
        float width = Image.Width * 72 / Image.HorizontalResolution;
        float height = Image.Height * 72 / Image.VerticalResolution;

        using StreamWriter sw = new(stream);
        // Write VML XML structure compliant with Excel format
        // Content includes v:shape and v:imagedata association
    }
}

Watermark Setup Workflow

1. Convert the image to byte[] and add it to the Workbook.
2. Create a VmlDrawing relationship and associate the image.
3. Set legacyDrawingHF on the Worksheet.

// Example of setting a Header watermark
sheet.Header.Center = HeaderFooter.PICTURE_FIELD.sequence;
sheet.GetCTWorksheet().legacyDrawingHF = new CT_LegacyDrawing {
    id = sheet.GetRelationId(drawing)
};

Change Log

• 2023-02-24 Initial version created.